﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace PhanMemDoVui.DataAccess
{
    public class ConnectData
    {
        private SqlConnection conn;
        private SqlDataAdapter dataAdapter;
        private DataTable dataTable;
        #region Constructor
        public ConnectData()
        {
            Connect();
        }
        #endregion
        #region Connect
        public void Connect()
        {
            string strConn = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DoVui.mdf;Initial Catalog=DoVui;Integrated Security=True;Trusted_Connection=Yes;User Instance=True";
            try
            {
                conn = new SqlConnection(strConn);
                conn.Open();
                conn.Close();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Lỗi: " + ex.Message, "QuizShow - FatalError", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
            }
        }
        #endregion
        #region GetDataTable
        // Lấy datatable từ câu truy vấn truyền vào
        public DataTable GetDataTable(string sql)
        {
            //tạo DataAdapter, thực hiện truy vấn
            try
            {
                dataAdapter = new SqlDataAdapter(sql, conn);
                dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Lỗi: " + ex.Message, "QuizShow - FatalError", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
            }
            return dataTable;
        }
        #endregion
        #region DemSoRecord
        // Dem so record cua mot bang nao do
        public int DemSoRecord(string table)
        {
            int soRecord = 0;
            if (!(table == "Exam" || table == "ExamUse" || table == "Profile" || table == "QuestionBank" || table == "QuestnAns" || table == "System"))
            {
                return -1;
            } 
            else
            {
                try
                {
                    conn.Open();
                    string sql = "Select count(*) From " + table;
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    soRecord = Int32.Parse(cmd.ExecuteScalar().ToString());
                    conn.Close();
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("Lỗi: " + ex.Message, "QuizShow - FatalError", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Application.Exit();
                }
                return soRecord;
            }
        }
        #endregion
        #region Return a number
        // Phuc vu cho viec can truy van va tra ve ket qua la 1 con so nao do de dung cho tinh toan
        public int ReturnANumber(string sql)
        {
            try
            {
                dataAdapter = new SqlDataAdapter(sql, conn);
                dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Lỗi: " + ex.Message, "QuizShow - FatalError", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
            }
            return Int32.Parse(dataTable.Rows[0][0].ToString());
        }
        #endregion
        #region XoaExam
        // Xoa tat ca cac record trong Exam va ExamUse co Ma = maExam va thoa man rang buoc tham chieu
        public bool XoaExam(int maExam)
        {
            string xoaExamUse = "Delete From ExamUse Where Ma = '" + maExam + "'";
            bool examUse = ExecuteNonQuery(xoaExamUse);
            string xoaExam = "Delete From Exam Where Ma = '" + maExam + "'";
            bool exam = ExecuteNonQuery(xoaExam);
            if (examUse == true && exam == true)
                return true;
            return false;
        }
#endregion
        #region INSERT_UPDATE_DELETE
        // Thực hiện các query cần thay đổi dữ liệu
        public bool ExecuteNonQuery(string sql)
        {
            int numRecordsEffect = 0;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                numRecordsEffect = cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Lỗi: " + ex.Message, "QuizShow - FatalError", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Exit();
            }
            finally
            {
                conn.Close();
            }
            if (numRecordsEffect > 0)
                return true;
            else
                return false;
        }
        #endregion
    }
}
